﻿CREATE FUNCTION [dbo].[fs_StateID]
(		@StateID int = null,
		@Access varchar(128) = 1
	)
RETURNS TABLE
AS

RETURN(

SELECT DISTINCT TOP 100 PERCENT t_State.ID, t_State.Pos
FROM t_State WITH(nolock)
INNER JOIN t_State_Group_Access WITH(nolock) ON t_State.ID = t_State_Group_Access.StateID
INNER JOIN UserGroups() UserGroups ON UserGroups.principal_id = t_State_Group_Access.principal_id
WHERE (t_State.ID = @StateID OR @StateID IS NULL) AND
			(t_State_Group_Access.StateAccessID  & @Access) > 0
ORDER BY Pos
)
